﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Hsinchu.Utility;

namespace Hsinchu.DAL
{
    public class DataProcess
    {

        #region 预处理方法（处理参数、转换等）

        /// <summary>
        /// 获得指定存储过程的参数集合
        /// </summary>
        /// <param name="_spName">指定的存储过程名称</param>
        /// <returns>SqlParameter参数集合</returns>
        /// <author>grokyao</author>
        /// <date>2008-12-22</date>
        public List<SqlParameter> getParameter(string _spName)
        {
            //用于返回的SqlParameter集合
            List<SqlParameter> _param = new List<SqlParameter>();
            try
            {
                using (SqlConnection connection = SqlHelper.getConn())
                {
                    SqlCommand cmd = connection.CreateCommand();
                    cmd.CommandText = _spName;
                    cmd.CommandType = CommandType.StoredProcedure;
                    //获得参数信息
                    SqlCommandBuilder.DeriveParameters(cmd);
                    for (int i = 0; i < cmd.Parameters.Count; i++)
                    {
                        //排除默认返回值
                        if (cmd.Parameters[i].ParameterName == "@RETURN_VALUE")
                        {
                            continue;
                        }
                        SqlParameter _p = new SqlParameter();
                        _p.SqlDbType = cmd.Parameters[i].SqlDbType;
                        _p.ParameterName = cmd.Parameters[i].ParameterName;
                        _p.Direction = cmd.Parameters[i].Direction;
                        _p.Size = cmd.Parameters[i].Size;
                        _param.Add(_p);
                    }
                    connection.Close();
                    return _param;
                }
            }
            catch (System.Data.SqlClient.SqlException sqlex)
            {
                throw sqlex;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// 将SqlDataReader转化为DataTable
        /// </summary>
        /// <param name="_sdr">传入的SqlDataReader对象</param>
        /// <author>grokyao</author>
        /// <date>2008-12-22</date>
        /// <returns>DataTable</returns>
        public DataTable ConvertDataReaderToDataTable(SqlDataReader _sdr)
        {
            DataTable datatable = new DataTable();
            DataTable schemaTable = _sdr.GetSchemaTable();
            //动态添加列
            try
            {
                for (int i = 0; i < _sdr.FieldCount; i++)
                {
                    DataColumn myDataColumn = new DataColumn();
                    myDataColumn.DataType = _sdr.GetFieldType(i);
                    myDataColumn.ColumnName = _sdr.GetName(i);
                    datatable.Columns.Add(myDataColumn);
                }

                //添加数据
                while (_sdr.Read())
                {
                    DataRow myDataRow = datatable.NewRow();
                    for (int i = 0; i < _sdr.FieldCount; i++)
                    {
                        myDataRow[i] = _sdr[i].ToString();
                    }
                    datatable.Rows.Add(myDataRow);
                    myDataRow = null;
                }
                schemaTable = null;
                _sdr.Close();
                _sdr.Dispose();
            }
            catch (Exception ex)
            {                
                throw new Exception("转换出错!", ex);
            }
            finally
            {
                _sdr.Close();
                _sdr.Dispose();
            }
            return datatable;
        }


        #endregion





        /// <summary>
        /// 根据存储过程名称返回DataTable
        /// </summary>
        /// <param name="_spName">存储过程名称</param>
        /// <author>grokyao</author>
        /// <date>2008-12-22</date>
        /// <returns>DataSet数据集</returns>
        public DataTable getDataTable(string _spName)
        {
            using (SqlConnection conn = SqlHelper.getConn())
            {
                return SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, _spName).Tables[0];
            }
        }


        /// <summary>
        /// 根据存储过程名称返回DataTable
        /// </summary>
        /// <param name="_spName">存储过程名称</param>
        /// <author>grokyao</author>
        /// <date>2008-12-22</date>
        /// <returns>int</returns>
        public int getAffectRows(string _spName)
        {
            using (SqlConnection conn = SqlHelper.getConn())
            {
                int _affectRows = SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, _spName);
                if (_affectRows < 0)
                {
                    _affectRows = 0;
                }
                return _affectRows;
            }
        }


        /*************************************************带参数*********************************************/

        /// <summary>
        /// 根据存储过程名称和参数返回DataTable
        /// </summary>
        /// <param name="_spName">存储过程名称</param>
        /// <param name="_value">参数对应的值集合</param>
        /// <author>grokyao</author>
        /// <date>2008-12-22</date>
        /// <returns>DataTable</returns>
        public DataTable getDataTable(string _spName, object[] _value)
        {
            using (SqlConnection conn = SqlHelper.getConn())
            {
                try
                {
                    //获得存储过程参数集合
                    List<SqlParameter> _listparam = getParameter(_spName);
                    SqlParameter[] sqlparam = new SqlParameter[_listparam.Count];
                    //匹配赋值
                    for (int i = 0; i < _listparam.Count; i++)
                    {
                        sqlparam[i] = _listparam[i];

                        #region 根据类型匹配
                        //字符型
                        if (sqlparam[i].SqlDbType == SqlDbType.VarChar && _value[i] is String)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //整数
                        if (sqlparam[i].SqlDbType == SqlDbType.Int && _value[i] is Int32)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //日期
                        if (sqlparam[i].SqlDbType == SqlDbType.DateTime && _value[i] is DateTime)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //货币
                        if (sqlparam[i].SqlDbType == SqlDbType.Money && _value[i] is Decimal)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //小数
                        if (sqlparam[i].SqlDbType == SqlDbType.Decimal && _value[i] is Decimal)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //浮点
                        if (sqlparam[i].SqlDbType == SqlDbType.Float && _value[i] is Double)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //可变字符
                        if (sqlparam[i].SqlDbType == SqlDbType.NVarChar && _value[i] is String)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //Guid
                        if (sqlparam[i].SqlDbType == SqlDbType.UniqueIdentifier && _value[i] is Guid)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //大整数
                        if (sqlparam[i].SqlDbType == SqlDbType.BigInt && _value[i] is Int64)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //布尔
                        if (sqlparam[i].SqlDbType == SqlDbType.Bit && _value[i] is Boolean)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //字符
                        if (sqlparam[i].SqlDbType == SqlDbType.Char && _value[i] is String)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }

                        //大文本
                        if (sqlparam[i].SqlDbType == SqlDbType.Text && _value[i] is String)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //
                        if (sqlparam[i].SqlDbType == SqlDbType.Variant && _value[i] is Object)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        #endregion

                    }
                    //获得SqlDataReader
                    SqlDataReader sdr = SqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, _spName, sqlparam);
                    DataTable Table = ConvertDataReaderToDataTable(sdr);
                    return Table;
                }
                catch (System.Data.SqlClient.SqlException sqlex)
                {
                    //Error.Log(sqlex.ToString());
                    return new DataTable();
                }
                catch (System.Exception ex)
                {
                    //Error.Log(ex.ToString());
                    return new DataTable();
                }
            }
        }



        /// <summary>
        /// 根据存储过程名称和参数返回DataSet
        /// </summary>
        /// <param name="_spName">存储过程名称</param>
        /// <param name="_value">参数对应的值集合</param>
        /// <author>grokyao</author>
        /// <date>2008-12-22</date>
        /// <returns>DataTable</returns>
        public DataSet getDataSet(string _spName, object[] _value)
        {
            using (SqlConnection conn = SqlHelper.getConn())
            {
                try
                {
                    //获得存储过程参数集合
                    List<SqlParameter> _listparam = getParameter(_spName);
                    SqlParameter[] sqlparam = new SqlParameter[_listparam.Count];
                    //匹配赋值
                    for (int i = 0; i < _listparam.Count; i++)
                    {
                        sqlparam[i] = _listparam[i];

                        #region 根据类型匹配
                        //字符型
                        if (sqlparam[i].SqlDbType == SqlDbType.VarChar && _value[i] is String)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //整数
                        if (sqlparam[i].SqlDbType == SqlDbType.Int && _value[i] is Int32)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //日期
                        if (sqlparam[i].SqlDbType == SqlDbType.DateTime && _value[i] is DateTime)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //货币
                        if (sqlparam[i].SqlDbType == SqlDbType.Money && _value[i] is Decimal)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //小数
                        if (sqlparam[i].SqlDbType == SqlDbType.Decimal && _value[i] is Decimal)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //浮点
                        if (sqlparam[i].SqlDbType == SqlDbType.Float && _value[i] is Double)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //可变字符
                        if (sqlparam[i].SqlDbType == SqlDbType.NVarChar && _value[i] is String)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //Guid
                        if (sqlparam[i].SqlDbType == SqlDbType.UniqueIdentifier && _value[i] is Guid)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //大整数
                        if (sqlparam[i].SqlDbType == SqlDbType.BigInt && _value[i] is Int64)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //布尔
                        if (sqlparam[i].SqlDbType == SqlDbType.Bit && _value[i] is Boolean)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //字符
                        if (sqlparam[i].SqlDbType == SqlDbType.Char && _value[i] is String)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }

                        //大文本
                        if (sqlparam[i].SqlDbType == SqlDbType.Text && _value[i] is String)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //
                        if (sqlparam[i].SqlDbType == SqlDbType.Variant && _value[i] is Object)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        #endregion

                    }
                    DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, _spName, sqlparam);
                    return ds;
                }
                catch (System.Data.SqlClient.SqlException sqlex)
                {
                    //Error.Log(sqlex.ToString());
                    return new DataSet();
                }
                catch (System.Exception ex)
                {
                    //Error.Log(ex.ToString());
                    return new DataSet();
                }
            }
        }


        /// <summary>
        /// 根据存储过程名称和参数返回受影响的行数
        /// </summary>
        /// <param name="_spName">存储过程名称</param>
        /// <param name="_value">参数对应的值集合</param>
        /// <author>grokyao</author>
        /// <date>2008-12-22</date>
        /// <returns>int</returns>
        public int getAffectRows(string _spName, object[] _value)
        {
            using (SqlConnection conn = SqlHelper.getConn())
            {
                try
                {
                    //获得存储过程参数集合
                    List<SqlParameter> _listparam = getParameter(_spName);
                    SqlParameter[] sqlparam = new SqlParameter[_listparam.Count];
                    //匹配赋值
                    for (int i = 0; i < _listparam.Count; i++)
                    {
                        sqlparam[i] = _listparam[i];

                        #region 根据类型匹配
                        //字符型
                        if (sqlparam[i].SqlDbType == SqlDbType.VarChar && _value[i] is String)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //整数
                        if (sqlparam[i].SqlDbType == SqlDbType.Int && _value[i] is Int32)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //日期
                        if (sqlparam[i].SqlDbType == SqlDbType.DateTime && _value[i] is DateTime)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //货币
                        if (sqlparam[i].SqlDbType == SqlDbType.Money && _value[i] is Decimal)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //小数
                        if (sqlparam[i].SqlDbType == SqlDbType.Decimal && _value[i] is Decimal)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //浮点
                        if (sqlparam[i].SqlDbType == SqlDbType.Float && _value[i] is Double)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //可变字符
                        if (sqlparam[i].SqlDbType == SqlDbType.NVarChar && _value[i] is String)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //Guid
                        if (sqlparam[i].SqlDbType == SqlDbType.UniqueIdentifier && _value[i] is Guid)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //大整数
                        if (sqlparam[i].SqlDbType == SqlDbType.BigInt && _value[i] is Int64)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //布尔
                        if (sqlparam[i].SqlDbType == SqlDbType.Bit && _value[i] is Boolean)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //字符
                        if (sqlparam[i].SqlDbType == SqlDbType.Char && _value[i] is String)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }

                        //大文本
                        if (sqlparam[i].SqlDbType == SqlDbType.Text && _value[i] is String)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        //
                        if (sqlparam[i].SqlDbType == SqlDbType.Variant && _value[i] is Object)
                        {
                            sqlparam[i].Value = _value[i];
                            continue;
                        }
                        #endregion
                    }
                    //获得SqlDataReader
                    int _affectRows = SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, _spName, sqlparam);
                    if (_affectRows < 0)
                    {
                        _affectRows = 0;
                    }
                    return _affectRows;
                }
                catch (System.Data.SqlClient.SqlException sqlex)
                {
                    //Error.Log(sqlex.ToString());
                    return 0;
                }
                catch (System.Exception ex)
                {
                    //Error.Log(ex.ToString());
                    return 0;
                }
            }
        }


        /// <summary>
        /// 根据存储过程读取返回值(还得改)
        /// </summary>
        /// <param name="_spName">存储过程名</param>        
        /// <returns>int</returns>
        /// <author>grokyao</author>
        /// <date>2009-1-12</date>
        public int getValue(string _spName)
        {
            using (SqlConnection conn = SqlHelper.getConn())
            {
                try
                {
                    SqlParameter[] _param = new SqlParameter[1];
                    _param[0] = new SqlParameter("@VerifyCircuitId", SqlDbType.Int);
                    _param[0].Direction = ParameterDirection.Output;
                    SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, _spName, _param);
                    return Convert.ToInt32(_param[0].Value);
                }
                catch (System.Data.SqlClient.SqlException sqlex)
                {
                    //Error.Log(sqlex.ToString());
                    return 0;
                }
                catch (System.Exception ex)
                {
                    //Error.Log(ex.ToString());
                    return 0;
                }
            }
        }



        /// <summary>
        /// 带事务的返回影响值
        /// </summary>
        /// <param name="_spName">存储过程</param>
        /// <param name="_objList">对象集合</param>
        /// <returns>int，0为失败，1为成功</returns>
        public int getAffectRowsWithTransaction(string _spName, List<object[]> _objList)
        {
            using (SqlConnection conn = SqlHelper.getConn())
            {
                SqlTransaction trans = null;
                try
                {
                    //创建事务
                    trans = conn.BeginTransaction();
                    int _affectRows = 0;
                    foreach (object[] _value in _objList)
                    {
                        //获得存储过程参数集合
                        List<SqlParameter> _listparam = getParameter(_spName);
                        SqlParameter[] sqlparam = new SqlParameter[_listparam.Count];
                        //匹配赋值
                        for (int i = 0; i < _listparam.Count; i++)
                        {
                            sqlparam[i] = _listparam[i];

                            #region 根据类型匹配
                            //字符型
                            if (sqlparam[i].SqlDbType == SqlDbType.VarChar && _value[i] is String)
                            {
                                sqlparam[i].Value = _value[i];
                                continue;
                            }
                            //整数
                            if (sqlparam[i].SqlDbType == SqlDbType.Int && _value[i] is Int32)
                            {
                                sqlparam[i].Value = _value[i];
                                continue;
                            }
                            //日期
                            if (sqlparam[i].SqlDbType == SqlDbType.DateTime && _value[i] is DateTime)
                            {
                                sqlparam[i].Value = _value[i];
                                continue;
                            }
                            //货币
                            if (sqlparam[i].SqlDbType == SqlDbType.Money && _value[i] is Decimal)
                            {
                                sqlparam[i].Value = _value[i];
                                continue;
                            }
                            //小数
                            if (sqlparam[i].SqlDbType == SqlDbType.Decimal && _value[i] is Decimal)
                            {
                                sqlparam[i].Value = _value[i];
                                continue;
                            }
                            //浮点
                            if (sqlparam[i].SqlDbType == SqlDbType.Float && _value[i] is Double)
                            {
                                sqlparam[i].Value = _value[i];
                                continue;
                            }
                            //可变字符
                            if (sqlparam[i].SqlDbType == SqlDbType.NVarChar && _value[i] is String)
                            {
                                sqlparam[i].Value = _value[i];
                                continue;
                            }
                            //Guid
                            if (sqlparam[i].SqlDbType == SqlDbType.UniqueIdentifier && _value[i] is Guid)
                            {
                                sqlparam[i].Value = _value[i];
                                continue;
                            }
                            //大整数
                            if (sqlparam[i].SqlDbType == SqlDbType.BigInt && _value[i] is Int64)
                            {
                                sqlparam[i].Value = _value[i];
                                continue;
                            }
                            //布尔
                            if (sqlparam[i].SqlDbType == SqlDbType.Bit && _value[i] is Boolean)
                            {
                                sqlparam[i].Value = _value[i];
                                continue;
                            }
                            //字符
                            if (sqlparam[i].SqlDbType == SqlDbType.Char && _value[i] is String)
                            {
                                sqlparam[i].Value = _value[i];
                                continue;
                            }

                            //大文本
                            if (sqlparam[i].SqlDbType == SqlDbType.Text && _value[i] is String)
                            {
                                sqlparam[i].Value = _value[i];
                                continue;
                            }
                            //
                            if (sqlparam[i].SqlDbType == SqlDbType.Variant && _value[i] is Object)
                            {
                                sqlparam[i].Value = _value[i];
                                continue;
                            }
                            #endregion
                        }
                        //获得SqlDataReader
                        _affectRows = SqlHelper.ExecuteNonQuery(conn, trans, CommandType.StoredProcedure, _spName, sqlparam);

                    }
                    if (_affectRows == -1)
                    {
                        _affectRows = 0;
                    }
                    //提交事务
                    trans.Commit();
                    return _affectRows;
                }
                catch (System.Data.SqlClient.SqlException sqlex)
                {
                    ErrorHandle.Log(sqlex.ToString());
                    trans.Rollback();
                    return 0;
                }
                catch (System.IndexOutOfRangeException iooe)
                {
                    ErrorHandle.Log(iooe.ToString());
                    trans.Rollback();
                    return 0;
                }
                catch (System.Exception ex)
                {
                    ErrorHandle.Log(ex.ToString());
                    trans.Rollback();
                    return 0;
                }
            }
        }



        /// <summary>
        /// 执行sql语句获得返回影响行数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>受影响行数</returns>
        public int getAffectRowsBySql(string sql)
        {
            using (SqlConnection conn = SqlHelper.getConn())
            {
                int _affectRows = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
                if (_affectRows < 0)
                {
                    _affectRows = 0;
                }
                return _affectRows;
            }
        }




        /// <summary>
        /// 生成sql
        /// </summary>
        /// <param name="tablename">要操作的表名</param>
        /// <param name="fieldname">字段名</param>
        /// <param name="fieldtype">字段类型</param>
        /// <param name="fieldsize">字段长度</param>
        /// <returns></returns>
        public string GenerationSql(string tablename, string fieldname, string fieldtype, string fieldsize)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("ALTER TABLE " + tablename + " ADD " + fieldname + " " + fieldtype);
            if (fieldtype != "varchar")
            {
                sql.Append(" " + fieldsize + " null");
            }
            else
            {
                sql.Append(" (" + fieldsize + ") null");
            }
            return sql.ToString();
        }

    }
}
